/***********************************************************************************************************************************
 * Name:	DB_Check-Integrity-All.sql
 * Author:	Frank Figearo (frank.figearo@us.bbaaviation.com/frank@sqlnerd.me)
 * Summary:	Execute DBCC CHECKDB on all databases. Continue executing after an error (not the default when run as a job step). If
 *			any errors are encountered, raise a generic error at the very end.
 */
SET ARITHABORT, IMPLICIT_TRANSACTIONS, XACT_ABORT OFF; SET ARITHIGNORE ON;
SET DEADLOCK_PRIORITY HIGH;
WHILE 0 < @@TRANCOUNT ROLLBACK;
DECLARE
	@db_name	SYSNAME,
	@errors		SMALLINT;
SET @errors= 0;
DECLARE list CURSOR LOCAL FAST_FORWARD FOR SELECT [name] FROM [master].[sys].[databases] WHERE [state] = 0 AND [name] <> N'TempDB' ORDER BY [database_id] DESC;
OPEN list;
WHILE (0=0) BEGIN;
  FETCH NEXT FROM list INTO @db_name;
  IF @@FETCH_STATUS <> 0 BREAK;
  PRINT CHAR(10)+REPLICATE('=', 132);
  PRINT @db_name;
  BEGIN TRY;
	DBCC CHECKDB(@db_name) WITH ALL_ERRORMSGS, NO_INFOMSGS;
  END TRY BEGIN CATCH;
	SET @errors= @errors+1;
	PRINT 'ERROR *** ERROR *** ERROR *** ERROR *** ERROR *** ERROR *** ERROR *** ERROR *** ERROR *** ERROR *** ERROR *** ERROR *** ERROR';
	SELECT ErrorNumber=ERROR_NUMBER(), ErrorMessage=ERROR_MESSAGE();
  END CATCH;
END;
CLOSE list;
DEALLOCATE list;

-- report any errors
IF 0 < @errors RAISERROR('DBCC CHECKDB()', 18, 0);